/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package com.ssm.changeunit.persistance;

import java.sql.*;
import java.util.List;
import java.util.ArrayList;
/**
 *
 * @author EuiHyun
 */
public class ChangeLogDAO extends AbstractChangeLogDAO {

    @Override
    public ChangeLogDTO find(String changeLogId) throws PersistanceException {

        ChangeLogDTO dto = null;
        
        // 검색 루틴 구현
        Connection conn = null; 
        Statement stmt = null; 
        ResultSet rs = null;
        
        //Create SQL Statement 
        String sql="select CHANGELOGID, FILEPATH, CONTENT from APP.CHANGELOG where CHANGELOGID = '"+changeLogId+"'";
        
        try {
            conn = dm.getConnection(); 
            conn.setAutoCommit(true);   
            stmt = conn.createStatement(); 
            rs = stmt.executeQuery(sql);
            
            if(rs.next()) {
                dto = new ChangeLogDTO();
                dto.setChangeLogId(rs.getString("CHANGELOGID"));
                dto.setFilePath(rs.getString("FILEPATH"));
                dto.setContent(rs.getBytes("CONTENT"));
            }
        }
        catch(SQLException e) { 
            throw new PersistanceException("Exception while executing ChangeLogDAO.find: "+ e, e);
        } 
        finally {
            if(rs != null) 
                try {rs.close(); } 
                catch(Exception ignore) { } 
            if(stmt != null) 
                try { stmt.close(); }
                catch(Exception ignore) { }  
            if(conn != null) 
                try { conn.close(); } 
                catch(Exception ignore) { } 
        } 
        // 검색 결과를 리턴 
        return dto; 
    }

    
    @Override
    public List<ChangeLogDTO> list() throws PersistanceException {

        //ChangeLogDTO dto = null;
        List<ChangeLogDTO> dtoArray = new ArrayList<ChangeLogDTO>();
        
        // 검색 루틴 구현
        Connection conn = null; 
        Statement stmt = null; 
        ResultSet rs = null;
        
        //Create SQL Statement 
        String sql="select CHANGELOGID, FILEPATH, CONTENT from APP.CHANGELOG ";
        
        try {
            conn = dm.getConnection(); 
            conn.setAutoCommit(true);   
            stmt = conn.createStatement(); 
            rs = stmt.executeQuery(sql);
            
            if(rs.next()) {
                ChangeLogDTO dto = new ChangeLogDTO();
                dto.setChangeLogId(rs.getString("CHANGELOGID"));
                dto.setFilePath(rs.getString("FILEPATH"));
                dto.setContent(rs.getBytes("CONTENT"));
                
                dtoArray.add(dto);
            }
        }
        catch(SQLException e) { 
            throw new PersistanceException("Exception while executing ChangeLogDAO.list: "+ e, e);
        } 
        finally {
            if(rs != null) 
                try {rs.close(); } 
                catch(Exception ignore) { } 
            if(stmt != null) 
                try { stmt.close(); }
                catch(Exception ignore) { }  
            if(conn != null) 
                try { conn.close(); } 
                catch(Exception ignore) { } 
        } 
        // 검색 결과를 리턴 
        return dtoArray; 
    }
    
    @Override
    public void insert(ChangeLogDTO changeLogDTO) throws PersistanceException {
        Connection conn = null; 
        PreparedStatement stmt = null;  
        
        //Create SQL Statement 
        //String sql="insert into APP.CHANGELOG (CHANGELOGID, FILEPATH, CONTENT ) " 
        //            + "values ('"+changeLogDTO.getChangeLogId()+"','"+changeLogDTO.getFilePath()+"','"+changeLogDTO.getContent()+"')";
        
//        String sql="insert into APP.CHANGELOG (CHANGELOGID, FILEPATH) " 
//                    + "values ('"+changeLogDTO.getChangeLogId()+"','"+changeLogDTO.getFilePath()+"')";
        
        String sql="insert into APP.CHANGELOG (CHANGELOGID, FILEPATH, CONTENT) " 
                    + "values (?,?,?)";
        
        try { 
            conn = dm.getConnection(); 
            conn.setAutoCommit(true);   
            stmt = conn.prepareStatement(sql);
            
            stmt.setString(1,changeLogDTO.getChangeLogId());
            stmt.setString(2,changeLogDTO.getFilePath());
            stmt.setBytes(3,changeLogDTO.getContent());
            
            stmt.executeUpdate(); 
        }
        catch(SQLException e) {
            throw new PersistanceException("Exception while executing ChangeLogDAO.insert: "+ e, e); 
        } 
        finally {
            if(stmt != null) 
                try { stmt.close(); } 
                catch(Exception ignore) { }  
            if(conn != null) 
                try { conn.close(); } 
                catch(Exception ignore) { } 
        }
    }

    @Override
    public void update(ChangeLogDTO changeLogDTO) throws PersistanceException {
        Connection conn = null; 
        PreparedStatement stmt = null;  
        
        //Create SQL Statement 
        //String sql="update APP.CHANGELOG set CHANGELOGID= '"+ changeLogDTO.getChangeLogId()
          //      + "' ,FILEPATH= '"+ changeLogDTO.getFilePath()
            //    + "' ,CONTENT= '"+ changeLogDTO.getContent()
              //  + "' where CHANGELOGID = '"+changeLogDTO.getChangeLogId()+"'";
        
        String sql="update APP.CHANGELOG set CHANGELOGID=? ,FILEPATH=? ,CONTENT=? where CHANGELOGID = ?";
        
        try { 
            conn = dm.getConnection(); 
            conn.setAutoCommit(true);   
            stmt = conn.prepareStatement(sql); 
            
            stmt.setString(1, changeLogDTO.getChangeLogId());
            stmt.setString(2, changeLogDTO.getFilePath());
            stmt.setBytes(3, changeLogDTO.getContent());
            stmt.setString(4, changeLogDTO.getChangeLogId());
            
            stmt.executeUpdate(); 
        }
        catch(SQLException e) {
            throw new PersistanceException("Exception while executing ChangeLogDAO.update: "+ e, e); 
        } 
        finally {
            if(stmt != null) 
                try { stmt.close(); } 
                catch(Exception ignore) { }  
            if(conn != null) 
                try { conn.close(); } 
                catch(Exception ignore) { } 
        }     
    }

    @Override
    public void delete(String changeLogId) throws PersistanceException {
        Connection conn = null; 
        Statement stmt = null;  
        
        //Create SQL Statement 
        String sql="delete from APP.CHANGELOG "  
                   +"WHERE CHANGELOGID = '"+changeLogId+"'";  
        
        try { 
            conn = dm.getConnection(); 
            conn.setAutoCommit(true);   
            stmt = conn.createStatement(); 
            stmt.executeUpdate(sql); 
        }
        catch(SQLException e) {
            throw new PersistanceException("Exception while executing ChangeLogDAO.delete: "+ e, e); 
        } 
        finally {
            if(stmt != null) 
                try { stmt.close(); } 
                catch(Exception ignore) { }  
            if(conn != null) 
                try { conn.close(); } 
                catch(Exception ignore) { } 
        } 
    }

}
